from database import *
from project import *
from person import *
from keyexpression import *
from crc import *

"""
Provides an interface to the client and handles its requests.
"""
class ClientRequestHandler(object):
    def __init__(self):
        self.database = Database()
        self._tryCreateTables()
        
    def _tryCreateTables(self):
        self._tryCreateProjectTables()
        self._tryCreatePeopleTables()
        self._tryCreateProjectMemberTables()
        self._tryCreateKeyExpressionTables()
        self._tryCreateClassTables()
        self._tryCreateUseCaseTables()
        self._tryCreateScrumTables()
        
        
        
    
    """ ------------ Projects ------------ """
    
    
    
    
    def _tryCreateProjectTables(self):
        self.database.execute("""
        create table if not exists Projects
        (
            ID integer primary key autoincrement,
            Name varchar(64),
            Summary text,
            Mandate text
        )""")
    
    def createProject(self, name, description = "", mandate = ""):
        """
        Creates a new project.
        returns: The newly created project.
        """
        project = self.getProjectByName(name)
        if project != None:
            raise Exception("Project already exists")
        
        id = self.database.insertRow("insert into Projects (Name, Summary, Mandate) values (?, ?, ?)",
                            name, description, mandate)
        return self.getProjectByID(id)
    
    def renameProject(self, id, name):
        """
        Renames a project.
        """
        self.database.execute("update Projects set Name = ? where id = ?", name, id)
        
    def setProjectSummary(self, id, summary):
        """
        Changes the summary of a project.
        """
        self.database.execute("update Projects set Summary = ? where id = ?", summary, id)
        
    def setProjectMandate(self, id, mandate):
        """
        Changes the mandate of a project.
        """
        self.database.execute("update Projects set Mandate = ? where id = ?", mandate, id)
    
    def updateProject(self, id, name, summary, mandate):
        """
        Updates a project's information.
        """
        self.database.execute("""
        update Projects
            set Name = ?, Summary = ?, Mandate = ?
            where id = ?
        """, name, summary, mandate, id)
    
    def getProjectByID(self, id):
        """
        Retrieves a Project by its id.
        returns: The project with that id.
        """
        row = self.database.getRow("select Name, Summary, Mandate from Projects where ID = ?", id)
        if row == None:
            return None
        return Project(id, row[0], row[1], row[2])
    
    def getProjectByName(self, name):
        """
        Gets a Project by its name.
        returns: The project with that name.
        """
        row = self.database.getRow("select ID, Summary, Mandate from Projects where Name = ?", name)
        if row == None:
            return None
        return Project(row[0], name, row[1], row[2])
    
    def getProjects(self):
        """
        Retrieves a list of all projects in this database.
        returns: A list of projects.
        """
        rows = self.database.getRows("select ID from Projects")
        projects = []
        for row in rows:
            projects.append(self.getProjectByID(row[0]))
        return projects
    
    
    
    
    """ ------------ People ------------ """
    
    
    
    
    def _tryCreatePeopleTables(self):
        self.database.execute("""
        create table if not exists People
        (
            ID integer primary key autoincrement,
            FirstName varchar(32),
            LastName varchar(32)
        )""")
        
    def createPerson(self, firstName, lastName):
        """
        Creates a new person in the database.
        """
        id = self.database.insertRow("insert into People (FirstName, LastName) values (?, ?)", firstName, lastName)
        return self.getPersonByID(id)

    def getPersonByID(self, id):
        """
        Retrieves a person from its identifier.
        """
        row = self.database.getRow("select FirstName, LastName from People where ID = ?", id)
        if row == None:
            return None
        return Person(id, row[0], row[1])



            
    """ ------------ ProjectMembers ------------ """
    
    


    def _tryCreateProjectMemberTables(self):
        self.database.execute("""
        create table if not exists ProjectMembers
        (
            ProjectID integer references Projects(ID),
            MemberID integer references People(ID)
        )""")
        
    def getProjectMembers(self, projectID):
        """
        Retrieves a list of project members.
        """
        rows = self.database.getRows("select ID from People where ID = ?", projectID)
        persons = []
        for row in rows:
            persons.append(getPersonByID(row[0]))
        return persons
    
    def deleteProjectMember(self,person):
        """
        delete someone
        """
        self.database.execute("delete from People where ID = ?", person.id)
        
    def addProjectMember(self, personID, projectID):
        """
        add members
        """ 
        self.database.execute("insert into ProjectMembers values (?,?)", projectID, personID) 

    """ ------------ KeyExpressions ------------ """
        
           
        
    def _tryCreateKeyExpressionTables(self):
        self.database.execute("""
        create table if not exists KeyExpressions
        (
            ProjectID integer references Projects(ID),
            Type varchar(16),
            Expression varchar(64),
            IsExplicit integer
         )""")
        

    def getProjectKeyExpressions(self, projectID, type = None):
        """
        Retrieves the key expressions for a given project
        """
        if type == None:
            rows = self.database.getRows("""
            select Type, Expression, IsExplicit
                from People
                where ProjectID = ?
            """, projectID)
        else:
            rows = self.database.getRows("""
            select Type, Expression, IsExplicit
                from People
                where ProjectID = ? and type = ?
            """, projectID, type)

        keyExpressions = []
        for row in rows:
            keyExpressions.append(KeyExpression(projectID, row[0], row[1], row[2]))
            
        return keyExpressions
        
    
    
    
    """ ------------ Classes ------------ """
        
        
        
        
    def _tryCreateClassTables(self):
        self.database.execute("""
        create table if not exists Classes
        (
            ID integer primary key autoincrement,
            ProjectID integer references Projects(ID),
            Name varchar(32),
            OwnerID integer references People(ID)
        )""")
        
        self.database.execute("""
        create table if not exists ClassResponsibilities
        (
            ClassID integer references Classes(ID),
            Description varchar(128)
        )""")
    
        self.database.execute("""
        create table if not exists ClassCollaborations
        (
            Class1ID integer references Classes(ID),
            Class2ID integer references Classes(ID)
        )""")
        
    def getProjectClasses(self, projectID):
        """
        Retrieves the classes for a given project
        """
        rows = self.database.getRows("""
        select ID, Name, OwnerID
            from Classes
            where ProjectID = ?
        """, projectID)

        classes = []
        for row in rows:
            classes.append(CRC(row[0], projectID, row[1], row[2]))

        return classes
    
    def getClassByID(self, id):
        """
        Retrieves a class by its identifier.
        """
        row = self.database.getRow("select ProjectID, Name, OwnerID from Classes where ID = ?", id)
        
        if row == None:
            return None
        
        crc = CRC(id, row[0], row[1], row[2])
            
        return crc
        
    def createClass(self, projectID, name, ownerID = 0):
        """
        Creates a new class in the database.
        """
        id = self.database.insertRow("insert into Classes (ProjectID, Name, OwnerID) values (?, ?, ?)",
                            projectID, name, ownerID)
        return self.getClassByID(id)
    
    
        
        
    
    """ ------------ UseCases ------------ """
        
        
        
        
    def _tryCreateUseCaseTables(self):
        self.database.execute("""
        create table if not exists UseCases
        (
            ProjectID integer references Projects(ID),
            Name varchar(50),
            Summary text
        )""")
        
    def createUseCase(self,projectID, Name , Summary):
        self.database.execute("insert into UseCases (Name, Summary) values (?, ?)", Name, Summary)
        
        
        
        
    """ ------------ Scrums ------------ """
        

        
        
        
    def _tryCreateScrumTables(self):
        self.database.execute("""
        create table if not exists Scrums
        (
            ID integer primary key,
            ProjectID integer references Projects(ID),
            Date date,
            MemberID integer references ProjectMembers(ID)
        )""")
        
        self.database.execute("""
        create table if not exists ScrumComponents
        (
            ScrumID integer references Scrums(ID),
            Type varchar(20),
            Description text
        )""")
        
        
        
        
        
if __name__ == "__main__":
    database = Database(":memory:")
    database._tryCreateTables()
    database.createProject("foo", "Does mostly bar")
    print "Projects:", database.getProjects()